<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
        <title>List Countries</title>
        <link rel="stylesheet" href="Style.css" type="text/css">
    </head>
    <?php
    //connect database
    include "ConnectDb.php";
    ?>
    <body>
        <?php
        include "Header.php";
        ?>
        <form id="form1" name="form1" method="post" action="">
            <table width="500" border="1" cellpadding="1" cellspacing="1">
                <tr>
                    <th>Country ID</th>
                    <th>Country Name</th>
                    <th>Number of users</th>
                </tr>
                <?php
                // check user name and password
                $query = "SELECT c.country_id, c.country_name, COUNT( u.user_id ) AS number_user
                            FROM countries c, users u
                            WHERE u.country_id = c.country_id
                            GROUP BY c.country_name
                            ORDER BY c.country_id ASC";

                $result = mysql_query($query, $mysql);
                if (mysql_num_rows($result) != 0) {
                    $count = 1;
                    while ($row = mysql_fetch_row($result)) {
                        echo "<tr>";
                        echo "<td>$row[0]</td>";
                        echo "<td>$row[1]</td>";
                        echo "<td><a href='NumberUserCountry.php?flag=$row[0]'>$row[2]</a></td>";
                        echo "</tr>";
                    }
                }
                ?>
            </table>
        </form>
    </body>
</html>
